//
//  DataBase.m
//  UILesson19_DataBase
//
//  Created by lanou3g on 15/11/9.
//  Copyright (c) 2015年 haiTeng. All rights reserved.
//

#import "DataBase.h"
#import <sqlite3.h>

@implementation DataBase


+ (DataBase *)shareDataBaseHandle{
    
    static DataBase *dataBase = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBase = [[DataBase alloc] init];
    });
    return dataBase;
}


//创建一个sqlite3 的(静态，全局)对象，以后所有的操作都针对它
static sqlite3 *db = nil;

#pragma mark    打开数据库
- (void)openDB{
    
    //如果，没被打开就打开
    if (db == nil) {
        //取到沙盒地址
        NSString *tempPath = NSTemporaryDirectory();
        NSLog(@"%@",tempPath);
        NSString *filePath = [tempPath stringByAppendingString:@"student.sqlite"];
        
        
        //在沙盒中去操作,打开路径下的数据库
        int result = sqlite3_open(filePath.UTF8String, &db);
        if (result == SQLITE_OK) {
            NSLog(@"打开数据库，成功");
            //写sql创建语句(创建表格)
            NSString *sql = @"CREATE  TABLE  IF NOT EXISTS ActivityModel (title TEXT PRIMARY KEY  NOT NULL , begin_time TEXT, end_time TEXT, address TEXT,category_name TEXT, participant_count INTEGER, wisher_count INTEGER, image TEXT, name TEXT, content TEXT)";
            //执行sql语句
            int result2 = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
            if (result2 == SQLITE_OK) {
                NSLog(@"创建表格成功");
            }else{
                NSLog(@"创建表格失败");
            }
            
        }else{
            NSLog(@"打开数据库失败");
        }
    
        
    }
   
    
}


#pragma mark   关闭数据库

- (void)closeDB{
    //如果数据库不为空，关闭它
    if (db != nil) {
        int result = sqlite3_close(db);
        if (result == SQLITE_OK) {
            NSLog(@"关闭数据库成功");
            db = nil; //同时把数据库对象置空
        }else{
            NSLog(@"关闭数据库失败");
        }
        
    }
    
    
    
    
}


#pragma mark    添加数据

- (void)insertActivity:(ActivityModel *)activity{
    //写sql添加语句
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO ActivityModel (title,begin_time,end_time,address,category_name,participant_count,wisher_count,image,name,content) VALUES ('%@','%@','%@','%@','%@','%d','%d','%@','%@','%@')",activity.title,activity.begin_time,activity.end_time,activity.address,activity.category_name,activity.participant_count,activity.wisher_count,activity.image,activity.name,activity.content];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加数据成功");
    }else{
        NSLog(@"添加数据失败");
    }
    

}


#pragma mark  根据title删除某个人


- (void)deleteActivity:(NSString *)activity{
    //写sql删除语句
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM ActivityModel WHERE title = '%@'",activity];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
    
    
}



#pragma mark   更新数据

- (void)updataActivity:(ActivityModel *)activity{
    
    NSString *sql = [NSString stringWithFormat:@"UPDATE ActivityModel SET begin_time = '%@' , end_time = '%@',address = '%@',category_name = '%@',participant_count = '%d',wisher_count = '%d',image = '%@',name = '%@',content = '%@' WHERE title = '%@'",activity.begin_time,activity.end_time,activity.address,activity.category_name,activity.participant_count,activity.wisher_count,activity.image,activity.name,activity.content,activity.title];
    int resulet = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (resulet == SQLITE_OK) {
        NSLog(@"更新数据成功");
    }else{
        NSLog(@"更新数据失败");
    }
    
}



#pragma mark  查找所有学生信息

- (NSMutableArray *)selectStudent{
    NSMutableArray *arrayAllStudents = [NSMutableArray array]; 
    
    NSString *sql = @"SELECT *FROM ActivityModel";
    //（第三个参数我们写-1自动计算sql的长度）
    //(第四个参数叫: 游标)
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"执行sql成功，开始找数据");
        //一行一行取查找数据，直到找完为止
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //在这里把它一个一个的数据拿出来
            //(找title 数据)
            const char *title1 = (const char *)sqlite3_column_text(stmt, 0);
            NSString *title = [NSString stringWithCString:title1 encoding:NSUTF8StringEncoding];
            
            //(找begin_time 数据)
            const char *begin_time1 = (const char *)sqlite3_column_text(stmt, 1);
            NSString *begin_time = [NSString stringWithCString:begin_time1 encoding:NSUTF8StringEncoding];
            
            //(找end_time 数据)
            const char *end_time1 = (const char *)sqlite3_column_text(stmt, 2);
            NSString *end_time = [NSString stringWithCString:end_time1 encoding:NSUTF8StringEncoding];
            
            //(找address 数据)
            const char *address1 = (const char *)sqlite3_column_text(stmt, 3);
            NSString *address = [NSString stringWithCString:address1 encoding:NSUTF8StringEncoding];
            
            //(找category_name 数据)
            const char *category_name1 = (const char *)sqlite3_column_text(stmt, 4);
            NSString *category_name = [NSString stringWithCString:category_name1 encoding:NSUTF8StringEncoding];
            
            //(找participant_count 数据)
            int participant_count = sqlite3_column_int(stmt, 5);
            
            //(找wisher_count 数据)
            int wisher_count = sqlite3_column_int(stmt, 6);
            
            //(找image 数据)
            const char *image1 = (const char *)sqlite3_column_text(stmt, 7);
            NSString *image = [NSString stringWithCString:image1 encoding:NSUTF8StringEncoding];
            
            //(找image 数据)
            const char *name1 = (const char *)sqlite3_column_text(stmt, 8);
            NSString *name = [NSString stringWithCString:name1 encoding:NSUTF8StringEncoding];
            
            //(找image 数据)
            const char *content1 = (const char *)sqlite3_column_text(stmt, 9);
            NSString *content = [NSString stringWithCString:content1 encoding:NSUTF8StringEncoding];
            
            
//            //把取到的数据放到数据模型中

            ActivityModel *model = [[ActivityModel alloc] init];
            model.title = title;
            model.begin_time = begin_time;
            model.end_time = end_time;
            model.address = address;
            model.category_name = category_name;
            model.participant_count = participant_count;
            model.wisher_count = wisher_count;
            model.image = image;
            model.name = name;
            model.content = content;
            
            [arrayAllStudents addObject:model];
        }
        
        
        
        
        
    }else{
        NSLog(@"执行sql失败");
        
    }
    //释放游标
    sqlite3_finalize(stmt);
    
    return arrayAllStudents;
    
}












@end
